In MS Access, while we merging two or more fields then the recordset of merge field contain garbage data after 255 characters. It is like a bug in MS Access. MS Access text field limit is between 0 to 255, but when, more than 255 characters are assigning to the recordset, it will be garbage or something else is overwritten. In this article, we reproduce and provide solution to handle with error. The bug causes damage to the MS Access Database.

To reproduce this error we need a table containing two fields, containing 255 characters. We also need a Form to represent. The MS Access form contains one field that will contains the characters after the first 255 are garbled in the Recordset Clone of the form as shown in Fig 1.1.

Resolution:

We can't simply fix this problem, because the limit of text field is 255. If we forcefully try to do fix it, it can cause of data corruption. But we can avoid this error. We have to take care when, any field that relies on a function that could yield more than 255 characters. This bug is also affected the ADO and DAO recordset. Characters after the first 255 are garbled in both ADO and DAO recordset. So we can only avoid the error. In this scenario we can use union query with a Memo field to persuade Access into treating the concatenated field as a Memo .That will be the source of Forms field. So using this way can avoid the error and the query result is as shown in Fig 1.2.

Recordset clone field calls the function:

Public Function CreateClone()
CreateClone = Forms!Form1.RecordsetClone! CorrectText
End Function

Union Field on Form has source:

SELECT Table1.ID, Table1.F1, Table1.F2, [F1] & [F2] AS CorrectText FROM Table1;